Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Reflection
Imports System.Runtime.CompilerServices
Imports System.Xml

Namespace DataAccess
    Friend Delegate Sub SQLExecution(ByVal ResultSet As SqlDataReader)
    Friend NotInheritable Class DataManager
        Implements IDisposable
        ' Events
        Public Event SQLExecutionEvent As SQLExecution

        ' Methods
        Public Sub New()
            'Me.SQLExecutionEvent = Nothing
            Me.mCommandType = CommandType.Text
            Me.SqlCmd = New SqlCommand
            Me.strConnectionString = ConfigurationManager.ConnectionStrings("DataConnectionString").ConnectionString
        End Sub

        Public Function Add(ByVal SqlPar As SqlParameter) As SqlParameter
            Me.SqlCmd.CommandType = CommandType.StoredProcedure
            Return Me.SqlCmd.Parameters.Add(SqlPar)
        End Function

        Private Function Add(ByVal SqlPar As SqlParameter, ByVal direction As ParameterDirection) As SqlParameter
            Me.SqlCmd.CommandType = CommandType.StoredProcedure
            SqlPar.Direction = direction
            Return Me.SqlCmd.Parameters.Add(SqlPar)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal value As Object) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, value))
        End Function

        Private Function Add(ByVal SqlPar As SqlParameter, ByVal direction As ParameterDirection, ByVal value As Object) As SqlParameter
            Me.SqlCmd.CommandType = CommandType.StoredProcedure
            SqlPar.Value = value
            SqlPar.Direction = direction
            Return Me.SqlCmd.Parameters.Add(SqlPar)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal direction As ParameterDirection) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType), direction)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal value As Object) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType), ParameterDirection.Input, value)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal value As Object, ByVal direction As ParameterDirection) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, value), direction)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal direction As ParameterDirection, ByVal value As Object) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType), direction, value)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal size As Integer, ByVal direction As ParameterDirection) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType, size), direction)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal size As Integer, ByVal value As Object) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType, size), ParameterDirection.Input, value)
        End Function

        Public Function Add(ByVal parameterName As String, ByVal sqlDbType As SqlDbType, ByVal size As Integer, ByVal direction As ParameterDirection, ByVal value As Object) As SqlParameter
            Return Me.Add(New SqlParameter(parameterName, sqlDbType, size), direction, value)
        End Function

        Public Sub Clear()
            Me.SqlCmd.Parameters.Clear()
        End Sub

        Public Function Contains(ByVal value As Object) As Boolean
            Return Me.SqlCmd.Parameters.Contains(value)
        End Function

        Public Function Contains(ByVal value As String) As Boolean
            Return Me.SqlCmd.Parameters.Contains(value)
        End Function



        Public Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                GC.SuppressFinalize(Me)
            End If
            Me.SqlCmd.Parameters.Clear()
            If ((Not Me.SqlCmd.Connection Is Nothing) AndAlso (Me.SqlCmd.Connection.State = ConnectionState.Open)) Then
                Me.SqlCmd.Connection.Close()
            End If
            Me.SqlCmd.Dispose()
        End Sub

        Public Function ExecuteDataTable(ByVal ExecuteString As String) As DataTable
            Dim table As DataTable
            Using Me.PrepareExecution(ExecuteString)
                Using adapter As SqlDataAdapter = New SqlDataAdapter(Me.SqlCmd)
                    Dim dataSet As New DataSet
                    adapter.Fill(dataSet)
                    If (dataSet.Tables.Count > 0) Then
                        Return dataSet.Tables.Item(0)
                    End If
                    table = Nothing
                End Using
            End Using
            Return table
        End Function

        Public Sub ExecuteforEvent(ByVal ExecuteString As String)
            Using Me.PrepareExecution(ExecuteString)
                'If (Not SQLExecutionEvent( Is Nothing) Then
                Using reader As SqlDataReader = Me.SqlCmd.ExecuteReader
                    RaiseEvent SQLExecutionEvent(reader)
                End Using
                'End If
            End Using
        End Sub

        Public Sub ExecuteforEvent(ByVal ExecuteString As String, ByVal behavior As CommandBehavior)
            Me.PrepareExecution(ExecuteString)
            'If (Not Me.SQLExecutionEvent Is Nothing) Then
            Using reader As SqlDataReader = Me.SqlCmd.ExecuteReader(behavior)
                RaiseEvent SQLExecutionEvent(reader)
            End Using
            'End If
        End Sub

        Public Function ExecuteNonQuery(ByVal ExecuteString As String) As Integer
            Using Me.PrepareExecution(ExecuteString)
                Return Me.SqlCmd.ExecuteNonQuery
            End Using
        End Function

        Public Function ExecuteReader(ByVal ExecuteString As String) As SqlDataReader
            Me.PrepareExecution(ExecuteString)
            Return Me.SqlCmd.ExecuteReader
        End Function

        Public Function ExecuteReader(ByVal ExecuteString As String, ByVal behavior As CommandBehavior) As SqlDataReader
            Me.PrepareExecution(ExecuteString)
            Return Me.SqlCmd.ExecuteReader(behavior)
        End Function

        Public Function ExecuteScalar(ByVal ExecuteString As String) As Object
            Using Me.PrepareExecution(ExecuteString)
                Return Me.SqlCmd.ExecuteScalar
            End Using
        End Function

        Public Function ExecuteXmlReader(ByVal ExecuteString As String) As XmlReader
            Me.PrepareExecution(ExecuteString)
            Return Me.SqlCmd.ExecuteXmlReader
        End Function

        Protected Overrides Sub Finalize()
            Try
                Me.Dispose(False)
            Finally
                MyBase.Finalize()
            End Try
        End Sub

        Public Function GetEnumerator() As IEnumerator
            Return Me.SqlCmd.Parameters.GetEnumerator
        End Function

        Public Function IndexOf(ByVal value As Object) As Integer
            Return Me.SqlCmd.Parameters.IndexOf(value)
        End Function

        Public Function IndexOf(ByVal parameterName As String) As Integer
            Return Me.SqlCmd.Parameters.IndexOf(parameterName)
        End Function

        Public Sub Insert(ByVal index As Integer, ByVal value As Object)
            Me.SqlCmd.Parameters.Insert(index, value)
        End Sub

        Private Function PrepareExecution(ByVal ExecuteString As String) As SqlConnection
            If (Me.SqlCmd Is Nothing) Then
                Me.SqlCmd = New SqlCommand
            End If
            If (Me.SqlCmd.Connection Is Nothing) Then
                Me.SqlCmd.Connection = New SqlConnection(Me.strConnectionString)
            ElseIf (Me.SqlCmd.Connection.ConnectionString.Length = 0) Then
                Me.SqlCmd.Connection.ConnectionString = Me.strConnectionString
            End If
            If (Me.SqlCmd.Connection.State <> ConnectionState.Open) Then
                Me.SqlCmd.Connection.Open()
            End If
            If (Me.SqlCmd.Parameters.Count > 0) Then
                Me.SqlCmd.CommandType = Me.mCommandType
            Else
                Me.SqlCmd.CommandType = Me.mCommandType
            End If
            Me.SqlCmd.CommandText = ExecuteString
            Return Me.SqlCmd.Connection
        End Function

        Public Sub Remove(ByVal value As Object)
            Me.SqlCmd.Parameters.Remove(value)
        End Sub

        Public Sub RemoveAt(ByVal index As Integer)
            Me.SqlCmd.Parameters.RemoveAt(index)
        End Sub

        Public Sub RemoveAt(ByVal parameterName As String)
            Me.SqlCmd.Parameters.RemoveAt(parameterName)
        End Sub

        Private Sub Dispose() Implements IDisposable.Dispose
            Me.Dispose(True)
        End Sub


        ' Properties
        Public Property CommandType() As CommandType
            Get
                Return Me.mCommandType
            End Get
            Set(ByVal value As CommandType)
                Me.mCommandType = value
            End Set
        End Property

        Public ReadOnly Property Count() As Integer
            Get
                Return Me.SqlCmd.Parameters.Count
            End Get
        End Property

        Default Public Property Item(ByVal index As Integer) As SqlParameter
            Get
                Return Me.SqlCmd.Parameters.Item(index)
            End Get
            Set(ByVal value As SqlParameter)
                Me.SqlCmd.Parameters.Item(index) = value
            End Set
        End Property

        Default Public Property Item(ByVal parameterName As String) As SqlParameter
            Get
                Return Me.SqlCmd.Parameters.Item(parameterName)
            End Get
            Set(ByVal value As SqlParameter)
                Me.SqlCmd.Parameters.Item(parameterName) = value
            End Set
        End Property


        ' Fields
        Private mCommandType As CommandType
        Private SqlCmd As SqlCommand
        Private strConnectionString As String
    End Class
End Namespace

